package org.cgs.web.beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.cgs.web.common.Utility;
import org.cgs.web.database.DatabaseConnector;

public final class InstructorHelperBean extends HelperBean
{
	private static final Logger LOGGER = Logger.getLogger(InstructorHelperBean.class);
	private String username;
	private String password;
	private String role;
	private boolean loggedIn;

	public InstructorHelperBean()
	{
		username = null;
		password = null;
		role = null;
		loggedIn = false;
	}

	//
	// Getter/Setter methods
	//
	
	public String getRole()
	{
		return role;
	}
	
	public void setRole(String role)
	{
		this.role = role;
	}

	public String getUsername()
	{
		return username;
	}

	public void setUsername(String username)
	{
		this.username = username;
	}

	public String getPassword()
	{
		return password;
	}

	public void setPassword(String password)
	{
		this.password = password;
	}

	public boolean isLoggedIn()
	{
		return loggedIn;
	}

	public void setLoggedIn(boolean loggedIn)
	{
		this.loggedIn = loggedIn;
	}

	//
	// Action methods
	//
	
	public void logout()
	{
		role = null;
		username = null;
		password = null;
		loggedIn = false;
	}

	public boolean hasAllInput()
	{
		return username != null && username.length() > 0 && password != null && password.length() > 0;
	}

	public List<String> authenticateInstructor() throws SQLException
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			con = DatabaseConnector.getInstance().getConnection();
			pstmt = con.prepareStatement("SELECT courseCode, role FROM instructorAuthorization WHERE netID=? AND password=?");
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();

			List<String> courses = new ArrayList<String>();
			while (rs.next())
			{
				courses.add(rs.getString("courseCode"));
			}

			return courses;
		}
		catch (SQLException e)
		{
			LOGGER.error("Exception authenticating the instructor " + username + " with password " + password, e);
			throw e;
		}
		finally
		{
			try
			{
				// Close the ResultSet
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt != null)
				{
					pstmt.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the connection
				if (con != null)
				{
					con.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up connection to the database", e);
			}
		}
	}

	public void getInstructorRole() throws SQLException
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			con = DatabaseConnector.getInstance().getConnection();
			pstmt = con.prepareStatement("SELECT role FROM instructorAuthorization WHERE netId=? AND courseCode=?");
			pstmt.setString(1, getUsername());
			pstmt.setString(2, getCourseCode());
			rs = pstmt.executeQuery();

			if (rs.next())
			{
				role = rs.getString("role");
			}
		}
		catch(SQLException e)
		{
			LOGGER.error("Exception thrown while atttempting to check if the instructor is an admin", e);
			throw e;
		}
		finally
		{
			try
			{
				// Close the ResultSet
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt != null)
				{
					pstmt.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the connection
				if (con != null)
				{
					con.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up connection to the database", e);
			}
		}
	}
	
	public boolean isInstructorAdmin()
	{
		return role.equalsIgnoreCase("ADMIN");
	}
	
	public List<School> getAllSchools() throws SQLException
	{
		return Utility.getAllSchoolsData(getCourseCode());
	}

	public School getSchool(String name) throws SQLException
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs2 = null;

		try
		{
			con = DatabaseConnector.getInstance().getConnection();
			pstmt = con.prepareStatement("SELECT * FROM school WHERE courseCode=? AND name=?");
			pstmt.setString(1, getCourseCode());
			pstmt.setString(2, name);
			rs = pstmt.executeQuery();

			if (rs.next())
			{
				School school = new School();
				school.setCourseCode(getCourseCode());
				school.setName(rs.getString("name"));
				school.setContact(rs.getString("contact"));
				school.setPhone(rs.getString("phone"));
				school.setNumStudents(rs.getInt("numStudents"));
				school.setAddress(rs.getString("address"));
				school.setTimeDetails(rs.getString("details"));

				String isFar = rs.getString("isFar");
				if (isFar != null && isFar.length() > 0)
				{
					school.setFarSchoolSet(true);
					school.setFarSchool(Boolean.parseBoolean(isFar));
				}

				//
				// Get the school times
				//

				pstmt2 = con.prepareStatement("SELECT * FROM school_times WHERE coursecode=? AND name=?");
				pstmt2.setString(1, getCourseCode());
				pstmt2.setString(2, school.getName());
				rs2 = pstmt2.executeQuery();

				while (rs2.next())
				{
					school.addTime(rs2.getString("time"));
				} // end while loop
				
				return school;
			}

			return null;
		}
		catch (SQLException e)
		{
			LOGGER.error("Exception thrown while atttempting to delete schools from DB", e);
			throw e;
		}
		finally
		{
			try
			{
				// Close the ResultSet
				if (rs2 != null)
				{
					rs2.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the ResultSet
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt2 != null)
				{
					pstmt2.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt != null)
				{
					pstmt.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the connection
				if (con != null)
				{
					con.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up connection to the database", e);
			}
		}
	}

	public List<Student> getAllStudents() throws SQLException
	{
		return Utility.getAllStudentsData(getCourseCode());
	}

	public Student getStudent(String studentNumber) throws SQLException
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs2 = null;

		try
		{
			con = DatabaseConnector.getInstance().getConnection();
			pstmt = con.prepareStatement("SELECT * FROM student WHERE courseCode=? AND studentNumber=?");
			pstmt.setString(1, getCourseCode());
			pstmt.setString(2, studentNumber);
			rs = pstmt.executeQuery();

			if (rs.next())
			{
				Student student = new Student();
				student.setCourseCode(getCourseCode());
				student.setName(rs.getString("name"));
				student.setStudentNumber(rs.getString("studentNumber"));
				student.setEmail(rs.getString("email"));
				student.setPhone(rs.getString("phone"));
				student.setLikeSubject(rs.getString("likeSubject"));
				student.setProgramAndYear(rs.getString("programAndyear"));
				student.setRecentCourse(rs.getString("recentCourse"));
				student.setTeachingAttraction(rs.getString("teachingAttraction"));
				student.setTeachingExperience(rs.getString("teachingExperience"));
				student.setTransportation(rs.getString("transportation"));
				student.setWhyInterested(rs.getString("whyInterested"));

				String hasVehicle = rs.getString("hasVehicle");
				if (hasVehicle != null && hasVehicle.length() > 0)
				{
					student.setHasVehicleSet(true);
					student.setHasVehicle(Boolean.parseBoolean(hasVehicle));
				}

				String hasTeachingExperience = rs.getString("hasTeachingExperience");
				if (hasTeachingExperience != null && hasTeachingExperience.length() > 0)
				{
					student.setHasTeachingExperienceSet(true);
					student.setHasTeachingExperience(Boolean.parseBoolean(hasTeachingExperience));
				}

				String hasSubjectKnowledge = rs.getString("hasSUbjectKnowledge");
				if (hasSubjectKnowledge != null && hasSubjectKnowledge.length() > 0)
				{
					student.setHasSubjectKnowledgeSet(true);
					student.setHasSubjectKnowledge(Boolean.parseBoolean(hasSubjectKnowledge));
				}

				//
				// Get the student times
				//

				pstmt2 = con.prepareStatement("SELECT * FROM student_times WHERE coursecode=? AND studentNumber=?");
				pstmt2.setString(1, getCourseCode());
				pstmt2.setString(2, student.getStudentNumber());
				rs2 = pstmt2.executeQuery();

				while (rs2.next())
				{
					student.addTime(rs2.getString("time"));
				} // end while loop
				
				return student;
			}

			return null;
		}
		catch (SQLException e)
		{
			LOGGER.error("Exception thrown while atttempting to delete schools from DB", e);
			throw e;
		}
		finally
		{
			try
			{
				// Close the ResultSet
				if (rs2 != null)
				{
					rs2.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the ResultSet
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt2 != null)
				{
					pstmt2.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt != null)
				{
					pstmt.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the connection
				if (con != null)
				{
					con.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up connection to the database", e);
			}
		}
	}
}